<?php
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

namespace LEA\UserBundle\SQL\dbmngt;

use Doctrine\DBAL\Connection;

class QueriesEtud{
	private $connection;

    public function __construct(Connection $dbalConnection)  {
        $this->connection = $dbalConnection;
    }


	function doGetTuteurInfoForStudent($student,$yearRef=null, $session)
	{
	    if ($yearRef==null) $yearRef=$session->get('REF_YEAR');
	    $query="select membre.nom, membre.prenom,membre.mail from
	                        membre inner join contrat on tuteurRef=profCle where
	                        etudRef = '".$student."' and anneeCle=".$yearRef." and notifAttribTuteur>0;";

	   $stmt = $this->connection->prepare($query);
	    $stmt->execute();

	    if ($stmt===FALSE)
	        return FALSE;
	    else {
	        $tuteur= $stmt->fetch();
	        return $tuteur;
	        }
	 }

	function doGetAltRefForStudent($student,$yearRef=null, $session)
	{
	    if ($yearRef==null) $yearRef=$session->get('REF_YEAR');
	    $query="select alternanceCle from contrat where etudRef='".$student."' and anneeCle=".$yearRef.";";

	    $stmt = $this->connection->prepare($query);
	    $stmt->execute();

	    if ($stmt===FALSE)
	        return FALSE;
	    else {
	        $cle= $stmt->fetch();
	        return $cle['alternanceCle'];
	    }
	}

	function doGetFormationForStudent($student,$yearRef=null, $session)
	{
	    if ($yearRef==null) $yearRef=$session->get('REF_YEAR');
	    $query="select formationRef from stalt2.etudiant e inner join stalt2.etudiant_groupe eg
	                    on e.etudCle=eg.etudRef inner join stalt2.groupe g on eg.groupeRef=g.groupeCle
	         where etudCle = '".$student."' and eg.annee = ".$yearRef ;
	    //echo $query;

	    $stmt = $this->connection->prepare($query);
	    $stmt->execute();

	    if ($stmt===FALSE)
	        return FALSE;
	    else {
	        $cle= $stmt->fetch();
	        return $cle['formationRef'];
	    }
	}

}
?>
